package com.es.hr.visitorscore;

import java.io.FileInputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.es.hr.shop.ShopBiz;
import com.es.util.db.Dao;
import com.opensymphony.webwork.interceptor.ServletRequestAware;
import com.opensymphony.xwork.ActionSupport;

public class ImportVisitorScoreExcelListAction extends ActionSupport implements
		ServletRequestAware {

	HttpServletRequest request;

	@SuppressWarnings("unchecked")
	public String execute() throws  Exception {
		request.setCharacterEncoding("utf-8");
		String intime = request.getAttribute("intime").toString();
		intime = intime.substring(0,7);
		String seesionId = request.getSession().getId()
				+ System.currentTimeMillis();
		String message = "";
		String result = "";
		VisitorScoreBiz biz = new VisitorScoreBiz();
		try {
			String path = System.getProperty("appDir") + "upload\\"
					+ (String) request.getAttribute("fileName");

			FileInputStream in = new FileInputStream(path);
			Workbook rwb = Workbook.getWorkbook(in);
			Sheet sheet = rwb.getSheet(0);
			int colCount = sheet.getColumns();
			int rowCount = sheet.getRows();

			ArrayList<ArrayList> queryParms = new ArrayList<ArrayList>();
			Cell temp = null;
			for (int row = 1; row < rowCount; ++row) {
				ArrayList<String> rowCell = new ArrayList<String>();
				for (int col = 0; col < colCount; ++col) {
					temp = sheet.getCell(col, row);
					if (col == 0 && sheet.getCell(0, row) != null
							&& sheet.getCell(0, row).getContents().equals("")) {
						break;
					}
					if (temp != null) {
						if(col == 2) {
							String v_date = temp.getContents();
							String year = v_date.substring(0,4);
							String month = v_date.substring(5,7);
							String date = v_date.substring(8,10);
							v_date = year + "-" + month + "-" + date;
							rowCell.add(v_date);
						} else {
							rowCell.add(temp.getContents());
						}
					}
				}
				if (rowCell.size() > 0) {
//					rowCell.add(String.valueOf(row));
					rowCell.add(seesionId);
					rowCell.add(intime);
					queryParms.add(rowCell);
				}

				// 每5000次提交一次 做批量提交，防止大数据内存溢出
				if (queryParms.size() % 5000 == 0) {
					if (!biz.importVisitorScoreList(queryParms)) {
						message = "文件导入失败！请确定是否是正确的门店信息模板。";
						request.setAttribute("message", message);
						return "false";
					}
					queryParms.clear();
				}
			}
			if (queryParms.size() > 0) {
				biz.importVisitorScoreList(queryParms);
				queryParms.clear();
			}
			
			//对导入的数据进行验证
			Dao dao = new Dao();
			String sql_area = "select visitor_area from hrm_mystery_visitor_tmp 	where v_sessionid='" + seesionId + "' and visitor_area not in (select distinct shop_area_value from hrm_shop_info)";
			String sql_shopId = "select shop_id from hrm_mystery_visitor_tmp where v_sessionid='" + seesionId + "' and shop_id not in (select distinct shop_code from hrm_shop_info)";
			String sql_ctm = "select v_oversee from hrm_mystery_visitor_tmp where v_sessionid='" + seesionId + "' and v_oversee not in (select distinct shop_ctm_name from hrm_shop_info)";
			ResultSet area_rs = dao.executePro(sql_area);
			ResultSet shopId_rs = dao.executePro(sql_shopId);
			ResultSet ctm_rs = dao.executePro(sql_ctm);
			if( area_rs.next() ) {
				String area_name = area_rs.getString(1);
				message = "区域 " + area_name + " 与商店信息的数据不一致！";
				this.request.setAttribute("message", message);
				result = "fail";
			} else if (shopId_rs.next()) {
				String shopId = shopId_rs.getString(1);
				message = "商店ID " + shopId + " 与商店信息的数据不一致！";
				this.request.setAttribute("message", message);
				result = "fail";
			} else if(ctm_rs.next()) {
				String ctmName = ctm_rs.getString(1);
				message = "督导 " + ctmName + " 与商店信息的数据不一致！";
				this.request.setAttribute("message", message);
				result = "fail";
			} else {
				message = "导入成功！";
				request.setAttribute("message", message);
				result = "success";
				biz.importTempDate(seesionId,intime);
			}
			biz.deleteTempDate(seesionId);
			
			return result;
//			ArrayList<HashMap<String, Object>> map = biz.importShopExcelListDo(
//					seesionId, intime);
//			if (map.size() == 1 && map.get(0).containsKey("rowNum")) {
//				message = "文件导入成功！共导入" + map.get(0).get("rowNum") + "条数据！";
//				request.setAttribute("message", message);
//				return "success";
//			} else {
//				request.setAttribute("errorMsg", map);
//				message = "文件导入失败！存在数据验证错误。";
//				request.setAttribute("message", message);
//				return "errorMsg";
//			}
		} catch (Exception e) {
			message = "文件导入失败！请重新尝试。";
			request.setAttribute("message", message);
			return "fail";
		}
	}

	public void setServletRequest(HttpServletRequest request) {
		this.request = request;
	}
}
